package com.hsnn.medstgmini.trading.catalog.dao.impl;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.Constants;
import com.hsnn.medstgmini.base.dict.dao.impl.BaseHibernateDAO;
import com.hsnn.medstgmini.base.dict.model.DicSys;
import com.hsnn.medstgmini.base.std.model.HospClassify;
import com.hsnn.medstgmini.base.std.model.HospLevel;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.base.model.ViewGoods;
import com.hsnn.medstgmini.trading.catalog.dao.HospCatalogDao;
import com.hsnn.medstgmini.trading.catalog.model.SubGoods;
import com.hsnn.medstgmini.trading.catalog.model.SubHospInfo;
import com.hsnn.medstgmini.util.AreaUtil;
import com.hsnn.medstgmini.util.DicSysUtil;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.ParseNumber;

/**
 * @category 医院商品目录查看
 * @author 蔡春龙
 * @date 2015年6月14日
 */
@Repository
public class HospCatalogDaoImpl extends BaseHibernateDAO implements HospCatalogDao {

	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	@Autowired
	private HibernateSqlUtil dao;

	/**
	 * @category 查询医疗机构列表
	 * @date 2015年7月7日
	 * @param hospInfo
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getHospList(SubHospInfo hospInfo, Pagination page) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer listSb = new StringBuffer();
		listSb.append("SELECT a.hosp_id hospId ");
		listSb.append(", a.hosp_code hospCode ");
		listSb.append(", a.hosp_name hospName ");
		listSb.append(", a.org_code orgCode ");
		listSb.append(", a.hosp_spel_code ");
		listSb.append(", a.hosp_classify hospClassifyId ");
		listSb.append(", a.hosp_level_id hospLevelId ");
		listSb.append(", c.ar_shortname areaName ");
		listSb.append(", d.hosp_type hospTypeName  ");
		listSb.append("FROM std_hospinfo a ")
		.append(" JOIN( SELECT")
		.append(" DISTINCT hosp_code ")
		.append(" FROM")
		.append(" bid_projhospital ")
		.append(" WHERE")
		.append(" proj_status = 1   ")
		.append(" AND status = 1 ")
		.append(" AND proj_starttime <= SYSDATE() ")
		.append(" AND proj_endtime >= SYSDATE()  ")
		.append(" ) h ")
		.append(" ON a.hosp_code = h.hosp_code  ");
		listSb.append("left join dic_area c on a.area_id = c.area_id ");
		listSb.append("left JOIN dic_hosptype d on a.hosp_type_id = d.hosp_type_id ");
		listSb.append("where a.status = 1 ");

		if (hospInfo != null) {
			String hospCode = hospInfo.getHospCode();
			if (StringUtils.isNotBlank(hospCode)) {
				listSb.append("and a.hosp_code = :hospCode ");
			}
			String hospName = hospInfo.getHospName();
			if (StringUtils.isNotBlank(hospName)) {
				listSb.append("and (a.hosp_name like :hospName or a.hosp_spel_code LIKE :hospName )");
			}
			String areaId = hospInfo.getAreaId();
			if (StringUtils.isNotBlank(areaId)) {
				listSb.append("and c.area_id like :areaId ");
			}
			String hospClassifyIds =  page.getConditions().get("hospClassifyIds");
			if ( hospClassifyIds  != null && hospClassifyIds !="") {
				listSb.append(" AND a.hosp_classify = :hospClassifyIds ");
			}
			String hospType =  page.getConditions().get("hospType");
			if ( hospType  != null && hospType !="") {
				listSb.append(" AND a.hosp_level_id = :hospType ");
			}
		}

		StringBuffer countSb = new StringBuffer();
		countSb.append("select count(*) from ( ");
		countSb.append(listSb.toString());
		countSb.append(") t ");

		SQLQuery countQuery = session.createSQLQuery(countSb.toString());
		SQLQuery listQuery = session.createSQLQuery(listSb.toString());

		if (hospInfo != null) {
			String hospCode = hospInfo.getHospCode();
			if (StringUtils.isNotBlank(hospCode)) {
				countQuery.setString("hospCode", hospCode);
				listQuery.setString("hospCode", hospCode);
			}
			String hospName = hospInfo.getHospName();
			if (StringUtils.isNotBlank(hospName)) {
				countQuery.setString("hospName", "%" + hospName + "%");
				listQuery.setString("hospName", "%" + hospName + "%");
			}
			String areaId = hospInfo.getAreaId();
			if (StringUtils.isNotBlank(areaId)) {
				countQuery.setString("areaId", areaId+"%");
				listQuery.setString("areaId", areaId+"%");
			}
			String hospClassifyIds =  page.getConditions().get("hospClassifyIds");
			if (StringUtils.isNotBlank(hospClassifyIds)) {
				countQuery.setString("hospClassifyIds", hospClassifyIds+"%");
				listQuery.setString("hospClassifyIds", hospClassifyIds+"%");
			}
			String hospType =  page.getConditions().get("hospType");
			if (StringUtils.isNotBlank(hospType)) {
				countQuery.setString("hospType", hospType);
				listQuery.setString("hospType", hospType);
			}
		}

		Long records = ((BigInteger) countQuery.uniqueResult()).longValue();
		page.setRecords(records);

		listQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		listQuery.setFirstResult(page.getFirstResult());
		listQuery.setMaxResults(page.getMaxResults());
		List<Map<String, Object>> list = listQuery.list();

		if (list != null) {
			for (Map<String, Object> map : list) {
				String hospLevelId = (String) map.get("hospLevelId");
				if (StringUtils.isNotBlank(hospLevelId)) {
					String hospLevelName = HospLevel.getName(hospLevelId);
					map.put("hospLevelName", hospLevelName);
				}

				
				if (map.get("hospClassifyId") != null) {
					Integer hospClassifyId = new Integer((Byte) map.get("hospClassifyId"));
					String hospClassifyName = HospClassify.getName(hospClassifyId);
					map.put("hospClassifyName", hospClassifyName);
				}

			}
		}

		page.setRows(list);

		return page;
	}

	/**
	 * @category 根据医疗机构查询商品列表
	 * @date 2015年7月7日
	 * @param goods
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getCatalogByHosp(SubGoods goods, Pagination page) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer listSb = new StringBuffer()
		 .append(" SELECT a.pro_price proPrice , ")
		 .append("       a.pro_count count , ")
		 .append("       a.hosp_pro_cat_id hospProCatId , ")
		 .append("       a.bak_pur_count bakPurCount , ")
		 .append("       a.hosp_code hospCode , ")
		 .append("       b.goods_code goodsCode , ")
		 .append("       b.drug_code drugCode , ")
		 .append("       b.qualityspec_id drugId , ")
		 .append("       b.qualityspec_name drugName , ")
		 .append("       b.form_name formName , ")
		 .append("       b.spec_name specName , ")
		 .append("       b.factor factor , ")
		 .append("       b.unit_name unit , ")
		 .append("       b.material_name materialName , ")
		 .append("       b.prod_compcode prodCompcode , ")
		 .append("       b.prod_compname prodCompname , ")
		 .append("       b.drug_cat drugCat , ")
		 .append("       b.proj_name projName , ")
		 .append("       b.drug_medicine drugMedicine , ")
		 .append("       b.pro_price_limit proPriceLimit , ")
		 .append("       b.retail_price_limit retailPriceLimit , ")
		 .append("       c.comp_code compId , ")
		 .append("       c.comp_name psCompName ,   ")
		 .append("       b.goods_source goodsSource ,b.drug_attr drugAttr, ")
		 .append("       b.is_longtime_plan isLongTimePlan,0 czbs,d.dic_name goodsSourceName  ")
		 .append("   FROM ")
		 .append("       pur_hospcatalog a  ")
		 .append("   JOIN ")
		 .append("       base_goods_item b  ")
		 .append("           ON a.goods_code = b.goods_code  ")
		 .append("   LEFT JOIN ")
		 .append("       std_compinfo c  ")
		 .append("           ON a.del_comp_code = c.comp_code  ")
		 .append(" left join dic_sysdic d on b.goods_source = d.dic_code and d.classify_code = 114 ")
		 .append("   where ")
		 .append("       a.hosp_code =:hospCode ")
		 .append("       and a.status = 1   ")
		 .append("       and b.drug_status = 1   ")
		 .append("       and ( ")
		 .append("           b.proj_status = 1  ")
		 .append("           or b.proj_status is null ")
		 .append("       )  ")
		 .append("       and b.status = 1  ");
		if (goods != null) {
			String goodsCode = goods.getGoodsCode();
			if (StringUtils.isNotBlank(goodsCode)) {
				listSb.append("and a.goods_code = :goodsCode ");
			}
			String drugCode = goods.getDrugCode();
			if (StringUtils.isNotBlank(drugCode)) {
				listSb.append("and b.drug_code = :drugCode ");
			}
			String drugName = goods.getDrugName();
			if (StringUtils.isNotBlank(drugName)) {
				listSb.append("and (b.qualityspec_name like :drugName or b.qualityspec_name_spel like :drugName ) ");
			}
			String formName = goods.getFormName();
			if (StringUtils.isNotBlank(formName)) {
				listSb.append("and b.form_name like :formName ");
			}
			String specName = goods.getSpecName();
			if (StringUtils.isNotBlank(specName)) {
				listSb.append("and b.spec_name like :specName ");
			}
			String prodComp = goods.getProdComp();
			if (StringUtils.isNotBlank(prodComp)) {
				listSb.append("and (b.prod_compname like :prodComp or b.prod_compname_spel like :prodComp)");
			}
			String psCompName = goods.getPsCompName();
			if (StringUtils.isNotBlank(psCompName)) {
//				listSb.append("and c.comp_name like :psCompName ");
				listSb.append(" and (c.comp_name like :psCompName or c.comp_spel_code like :psCompSpelCode )");
			}
			String projName = goods.getProjName();
			if (StringUtils.isNotBlank(projName)) {
				listSb.append("and b.proj_name like :projName ");
			}
			Integer isLongTimePlan = goods.getIsLongTimePlan();
			if (isLongTimePlan != null) {
				listSb.append("and b.goods_source=5 and b.is_longtime_plan = :isLongTimePlan ");
			}
			String goodsSource = goods.getGoodsSource();
			if (StringUtils.isNotBlank(goodsSource)) {
				listSb.append("and b.goods_source= :goodsSource ");
			}
		}

		StringBuffer countSb = new StringBuffer();
		countSb.append("select count(*) from ( ");
		countSb.append(listSb.toString());
		countSb.append(") t ");

		SQLQuery countQuery = session.createSQLQuery(countSb.toString());
		SQLQuery listQuery = session.createSQLQuery(listSb.toString());

		countQuery.setString("hospCode", goods.getHospCode());
		listQuery.setString("hospCode", goods.getHospCode());

		if (goods != null) {
			String goodsCode = goods.getGoodsCode();
			if (StringUtils.isNotBlank(goodsCode)) {
				countQuery.setString("goodsCode", goodsCode);
				listQuery.setString("goodsCode", goodsCode);
			}
			String drugCode = goods.getDrugCode();
			if (StringUtils.isNotBlank(drugCode)) {
				countQuery.setString("drugCode", drugCode);
				listQuery.setString("drugCode", drugCode);
			}
			String drugName = goods.getDrugName();
			if (StringUtils.isNotBlank(drugName)) {
				countQuery.setString("drugName", "%" + drugName + "%");
				listQuery.setString("drugName", "%" + drugName + "%");
			}
			String formName = goods.getFormName();
			if (StringUtils.isNotBlank(formName)) {
				countQuery.setString("formName", "%" + formName + "%");
				listQuery.setString("formName", "%" + formName + "%");
			}
			String specName = goods.getSpecName();
			if (StringUtils.isNotBlank(specName)) {
				countQuery.setString("specName", "%" + specName + "%");
				listQuery.setString("specName", "%" + specName + "%");
			}
			String prodComp = goods.getProdComp();
			if (StringUtils.isNotBlank(prodComp)) {
				countQuery.setString("prodComp", "%" + prodComp + "%");
				listQuery.setString("prodComp", "%" + prodComp + "%");
			}
			String psCompName = goods.getPsCompName();
			if (StringUtils.isNotBlank(psCompName)) {
				countQuery.setString("psCompName", "%" + psCompName.trim() + "%");
				listQuery.setString("psCompName", "%" + psCompName.trim() + "%");
				countQuery.setString("psCompSpelCode", "%" + psCompName.trim().toUpperCase() + "%");
				listQuery.setString("psCompSpelCode", "%" + psCompName.trim().toUpperCase() + "%");
			}
			String projName = goods.getProjName();
			if (StringUtils.isNotBlank(projName)) {
				countQuery.setString("projName", "%" + projName + "%");
				listQuery.setString("projName", "%" + projName + "%");
			}
			Integer isLongTimePlan = goods.getIsLongTimePlan();
			if (isLongTimePlan != null) {
				countQuery.setInteger("isLongTimePlan", isLongTimePlan);
				listQuery.setInteger("isLongTimePlan", isLongTimePlan);
			}
			
			String goodsSource = goods.getGoodsSource();
			if (StringUtils.isNotBlank(goodsSource)) {
				listSb.append("and b.goods_source= :goodsSource ");
				countQuery.setString("goodsSource", goodsSource);
				listQuery.setString("goodsSource", goodsSource);
			}
		}

		Long records = ((BigInteger) countQuery.uniqueResult()).longValue();
		page.setRecords(records);

		listQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		listQuery.setFirstResult(page.getFirstResult());
		listQuery.setMaxResults(page.getMaxResults());
		List<Map<String, Object>> list = listQuery.list();

		if (list != null && !list.isEmpty()) {
			for (Map<String, Object> map : list) {
				String drugCat = map.get("drugCat").toString();
				if (StringUtils.isNotBlank(drugCat)) {
					DicSys dic = DicSysUtil.getDicByClassCodeAndDicCode(
							Constants.DRUG_CAT, drugCat);
					if (dic != null) {
						String drugCatName = dic.getDicName();
						map.put("drugCatName", drugCatName);
					}
				}

				BigDecimal proPrice = (BigDecimal) map.get("proPrice");
				if (proPrice != null) {
					map.put("proPrice",
							ParseNumber.toStrDecimal(proPrice.doubleValue()));
				}

				BigDecimal proPriceLimit = (BigDecimal) map
						.get("proPriceLimit");
				if (proPriceLimit != null) {
					map.put("proPriceLimit", ParseNumber
							.toStrDecimal(proPriceLimit.doubleValue()));
				}

				BigDecimal retailPriceLimit = (BigDecimal) map
						.get("retailPriceLimit");
				if (retailPriceLimit != null) {
					map.put("retailPriceLimit", ParseNumber
							.toStrDecimal(retailPriceLimit.doubleValue()));
				}
				
				String compId = (String) map.get("compId");
				if (StringUtils.isBlank(compId)) {
					map.put("compId", "");
				}
			}
		}

		page.setRows(list);

		return page;
	}

	/**
	 * @category 查询商品列表
	 * @date 2015年7月7日
	 * @param goods
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getGoodsList(SubGoods goods, Pagination page) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer()
		.append("  SELECT b.goods_code goodsCode ,")
		.append(" b.drug_code drugCode ,")
		.append(" b.qualityspec_name drugName ,")
		.append(" b.form_name formName ,")
		.append(" b.spec_name specName ,")
		.append(" b.factor factor ,")
		.append(" b.unit_name unit ,")
		.append(" b.material_name materialName ,")
		.append(" b.prod_compcode prodCompcode ,")
		.append(" b.prod_compname prodCompname ,")
		.append(" b.drug_cat drugCat ,")
		.append(" b.proj_name projName ,")
		.append(" b.drug_medicine drugMedicine,b.pro_price_limit proPriceLimit ,")
		.append(" b.retail_price_limit retailPriceLimit ,")
		.append(" b.goods_source goodsSource ,")
		.append(" b.is_longtime_plan isLongtimePlan ");
		
		StringBuffer listSb =new StringBuffer()
		.append(" FROM base_goods_item b ,(select  distinct goods_code  from  pur_hospcatalog )  c ")
		.append(" where  b.goods_code =c.goods_code and b.drug_status = 1   ")
		.append(" and  b.status = 1   ")
		.append(" and  b.proj_status=1")
		.append(" and  b.qualityspec_status=1")
		.append(" and  SYSDATE() between b.proj_starttime and b.proj_endtime  ")
		.append(" and ( b.proj_status = 1 or b.proj_status is null ) ");
		

		if (goods != null) {
			String areaId = goods.getAreaId();
			if (StringUtils.isNotBlank(areaId)) {
				listSb.append("and b.area_id like :areaId ");
			}
			String goodsCode = goods.getGoodsCode();
			if (StringUtils.isNotBlank(goodsCode)) {
				listSb.append("and b.goods_code = :goodsCode ");
			}
			String drugCode = goods.getDrugCode();
			if (StringUtils.isNotBlank(drugCode)) {
				listSb.append("and b.drug_code = :drugCode ");
			}
			String drugName = goods.getDrugName();
			if (StringUtils.isNotBlank(drugName)) {
				listSb.append("and b.qualityspec_name like :drugName ");
			}
			String prodComp = goods.getProdComp();
			if (StringUtils.isNotBlank(prodComp)) {
				listSb.append("and b.prod_compname like :prodComp ");
			}
			String projName = goods.getProjName();
			if (StringUtils.isNotBlank(projName)) {
				listSb.append("and b.proj_name like :projName ");
			}
			Integer isLongTimePlan = goods.getIsLongTimePlan();
			if (null !=isLongTimePlan) {
				listSb.append("and b.goods_source =5 and b.is_longtime_plan = :isLongTimePlan ");
			}
		}

		StringBuffer countSb = new StringBuffer();
		countSb.append("select count(*) ")
		.append(listSb.toString());
	

		SQLQuery countQuery = session.createSQLQuery(countSb.toString());
		SQLQuery listQuery = session.createSQLQuery(sql.append(listSb).toString());

		if (goods != null) {
			if (StringUtils.isNotBlank(goods.getAreaId())) {
				
				String areaIdTemp ="";
				if(goods.getAreaId().length()!=2){//中心查看省级地区条件
					String[] areaIdes= AreaUtil.getSuperArea(goods.getAreaId());
					for(int i=0;i<areaIdes.length;i++){
						areaIdTemp=StringUtils.isNotBlank(areaIdes[1])?areaIdes[1]:areaIdes[0];
					}
				}else{
					areaIdTemp =goods.getAreaId();
				}
				areaIdTemp = AreaUtil.escapeAreaId(areaIdTemp).replace("_","");
				countQuery.setString("areaId", areaIdTemp+"%");
				listQuery.setString("areaId", areaIdTemp+"%");
			}
			String goodsCode = goods.getGoodsCode();
			if (StringUtils.isNotBlank(goodsCode)) {
				countQuery.setString("goodsCode", goodsCode);
				listQuery.setString("goodsCode", goodsCode);
			}
			String drugCode = goods.getDrugCode();
			if (StringUtils.isNotBlank(drugCode)) {
				countQuery.setString("drugCode", drugCode);
				listQuery.setString("drugCode", drugCode);
			}
			String drugName = goods.getDrugName();
			if (StringUtils.isNotBlank(drugName)) {
				countQuery.setString("drugName", "%" + drugName + "%");
				listQuery.setString("drugName", "%" + drugName + "%");
			}
			String prodComp = goods.getProdComp();
			if (StringUtils.isNotBlank(prodComp)) {
				countQuery.setString("prodComp", "%" + prodComp + "%");
				listQuery.setString("prodComp", "%" + prodComp + "%");
			}
			String projName = goods.getProjName();
			if (StringUtils.isNotBlank(projName)) {
				countQuery.setString("projName", "%" + projName + "%");
				listQuery.setString("projName", "%" + projName + "%");
			}
			Integer isLongTimePlan = goods.getIsLongTimePlan();
			if ( null != isLongTimePlan) {
				countQuery.setInteger("isLongTimePlan", isLongTimePlan);
				listQuery.setInteger("isLongTimePlan", isLongTimePlan);
			}
		}

		Long records = ((BigInteger) countQuery.uniqueResult()).longValue();
		page.setRecords(records);

		listQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		listQuery.setFirstResult(page.getFirstResult());
		listQuery.setMaxResults(page.getMaxResults());
		List<Map<String, Object>> list = listQuery.list();

		if (list != null && !list.isEmpty()) {
			for (Map<String, Object> map : list) {
				String drugCat =  map.get("drugCat").toString();
				if (StringUtils.isNotBlank(drugCat)) {
					DicSys dic = DicSysUtil.getDicByClassCodeAndDicCode(
							Constants.DRUG_CAT, drugCat);
					if (dic != null) {
						String drugCatName = dic.getDicName();
						map.put("drugCatName", drugCatName);
					}
				}

				BigDecimal proPriceLimit = (BigDecimal) map
						.get("proPriceLimit");
				if (proPriceLimit != null) {
					map.put("proPriceLimit", ParseNumber
							.toStrDecimal(proPriceLimit.doubleValue()));
				}

				BigDecimal retailPriceLimit = (BigDecimal) map
						.get("retailPriceLimit");
				if (retailPriceLimit != null) {
					map.put("retailPriceLimit", ParseNumber
							.toStrDecimal(retailPriceLimit.doubleValue()));
				}
			}
		}

		page.setRows(list);

		return page;
	}

	/**
	 * @category 查询商品下的医疗机构采购目录列表(中心根据商品查看旗下的医疗机构)
	 * @date 2015年7月7日
	 * @param hospInfo
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getCatalogByGoods(SubHospInfo hospInfo, Pagination page) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer listSb = new StringBuffer();
		listSb.append("SELECT distinct a.hosp_id hospId ");
		listSb.append(", a.hosp_code hospCode ");
		listSb.append(", a.hosp_name hospName ");
		listSb.append(", a.org_code orgCode ");
		listSb.append(", a.hosp_classify hospClassifyId ");
		listSb.append(", a.hosp_level_id hospLevelId ");
		listSb.append(", b.pro_price proPrice ");
		listSb.append(", b.pro_count count ");
		listSb.append(", b.bak_pur_count bakPurCount ");
		listSb.append(", c.ar_shortname areaName ");
		listSb.append(", d.hosp_type hospTypeName ");
		listSb.append(", e.comp_name psCompName ");
		listSb.append("FROM std_hospinfo a ");
		listSb.append("JOIN ( select goods_code,del_comp_code,hosp_code,pro_price,pro_count,bak_pur_count from pur_hospcatalog where goods_code = :goodsCode ");
		if (hospInfo != null) {
			String hospCode = hospInfo.getHospCode();
			if (StringUtils.isNotBlank(hospCode)) {
				listSb.append(" and hosp_code =:hospCode ");
			}
		}
		listSb.append(" )b on a.hosp_code = b.hosp_code ");
		listSb.append("left JOIN dic_area c on a.area_id = c.area_id ");
		listSb.append("left JOIN dic_hosptype d on a.hosp_type_id = d.hosp_type_id ");
		listSb.append("LEFT JOIN std_compinfo e ON b.del_comp_code = e.comp_code ");
		listSb.append("WHERE a.status = 1 ");

		if (hospInfo != null) {
			String hospName = hospInfo.getHospName();
			if (StringUtils.isNotBlank(hospName)) {
				listSb.append("and a.hosp_name like :hospName ");
			}
			String areaId = hospInfo.getAreaId();
			if (StringUtils.isNotBlank(areaId)) {
				listSb.append("and a.area_id like :areaId ");
			}
		}

		StringBuffer countSb = new StringBuffer();
		countSb.append("select count(*) from ( ");
		countSb.append(listSb.toString());
		countSb.append(") t ");

		SQLQuery countQuery = session.createSQLQuery(countSb.toString());
		SQLQuery listQuery = session.createSQLQuery(listSb.toString());

		countQuery.setString("goodsCode", hospInfo.getGoodsCode());
		listQuery.setString("goodsCode", hospInfo.getGoodsCode());

		if (hospInfo != null) {
			String hospCode = hospInfo.getHospCode();
			if (StringUtils.isNotBlank(hospCode)) {
				countQuery.setString("hospCode", hospCode);
				listQuery.setString("hospCode", hospCode);
			}
			String hospName = hospInfo.getHospName();
			if (StringUtils.isNotBlank(hospName)) {
				countQuery.setString("hospName", "%" + hospName + "%");
				listQuery.setString("hospName", "%" + hospName + "%");
			}
			String areaId = hospInfo.getAreaId();
			if (StringUtils.isNotBlank(areaId)) {
				countQuery.setString("areaId", areaId+"%");
				listQuery.setString("areaId", areaId+"%");
			}
		}

		Long records = ((BigInteger) countQuery.uniqueResult()).longValue();
		page.setRecords(records);

		listQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		listQuery.setFirstResult(page.getFirstResult());
		listQuery.setMaxResults(page.getMaxResults());
		List<Map<String, Object>> list = listQuery.list();

		if (list != null) {
			for (Map<String, Object> map : list) {
				String hospLevelId = (String) map.get("hospLevelId");
				if (StringUtils.isNotBlank(hospLevelId)) {
					String hospLevelName = HospLevel.getName(hospLevelId);
					map.put("hospLevelName", hospLevelName);
				}

				Integer hospClassifyId = new Integer(
						(Byte) map.get("hospClassifyId"));
				if (hospClassifyId != null) {
					String hospClassifyName = HospClassify
							.getName(hospClassifyId);
					map.put("hospClassifyName", hospClassifyName);
				}

				BigDecimal proPrice = (BigDecimal) map.get("proPrice");
				if (proPrice != null) {
					map.put("proPrice",
							ParseNumber.toStrDecimal(proPrice.doubleValue()));
				}

			}
		}

		page.setRows(list);

		return page;
	}

	/**
	 * @category 查询goods信息
	 * @date 2015年7月8日
	 * @param goodsCode
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public ViewGoods getViewGoodsByCode(String goodsCode) {
		Session session = sessionFactory.getCurrentSession();
		if (StringUtils.isNotBlank(goodsCode)) {
			Criteria criteria = session.createCriteria(ViewGoods.class);
			criteria.add(Restrictions.eq("goodsCode", goodsCode));
			List<ViewGoods> list = criteria.list();
			if (list != null && !list.isEmpty()) {
				return list.get(0);
			}
		}
		return null;
	}

	@Override
	public String getAreaIdByOrgId(String orgId) {
		SQLQuery  query = getSession().createSQLQuery("select area_id from std_healthbureau where hea_bur_id=:orgId");
		query.setParameter("orgId", orgId);
		Object obj = query.uniqueResult(); 
		
		return obj!=null?obj.toString():null;
	}

	@Override
	public Pagination getHospListBySC(SubHospInfo hospInfo, Pagination page) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer listSb = new StringBuffer()
		.append(" SELECT a.hosp_id hospId,")
		.append(" 	a.hosp_code hospCode,")
		.append(" 	a.hosp_name hospName,")
		.append(" 	a.org_code orgCode,")
		.append(" 	a.hosp_classify hospClassifyId,")
		.append(" 	a.hosp_level_id hospLevelId,")
		.append(" 	c.ar_shortname areaName,")
		.append(" 	d.hosp_type hospTypeName")
		.append(" FROM")
		.append(" 	std_hospinfo a")
		.append(" JOIN (")
		.append(" SELECT DISTINCT a.hosp_code                                        ")
		.append(" FROM bid_projhospital a                                            ")
		.append(" JOIN 		bid_projdrugs b ON a.proj_id = b.proj_id                 ")
		.append(" 		    AND b.proj_status = 1                                    ")
		.append(" 			AND b.status = 1                                         ")
		.append(" 			AND b.comp_code = :compCode                              ")
		.append(" 			AND SYSDATE() BETWEEN b.proj_starttime AND b.proj_endtime")
		.append(" WHERE a.proj_status = 1                                            ")
		.append(" AND a.STATUS = 1                                                   ")
		.append(" AND a.proj_starttime <= SYSDATE()                                  ")
		.append(" AND a.proj_endtime >= SYSDATE()                                    ")
		.append(" ) b ON a.hosp_code = b.hosp_code")
		.append(" LEFT JOIN dic_area c ON a.area_id = c.area_id")
		.append(" LEFT JOIN dic_hosptype d ON a.hosp_type_id = d.hosp_type_id")
		.append(" WHERE a.status = 1 ");
		if (hospInfo != null) {
			String hospCode = hospInfo.getHospCode();
			if (StringUtils.isNotBlank(hospCode)) {
				listSb.append("and a.hosp_code = :hospCode ");
			}
			String hospName = hospInfo.getHospName();
			if (StringUtils.isNotBlank(hospName)) {
				listSb.append("and a.hosp_name like :hospName ");
			}
			String areaId = hospInfo.getAreaId();
			if (StringUtils.isNotBlank(areaId)) {
				listSb.append("and a.area_id like :areaId ");
			}
		}

		StringBuffer countSb = new StringBuffer();
		countSb.append("select count(*) from ( ");
		countSb.append(listSb.toString());
		countSb.append(") t ");

		SQLQuery countQuery = session.createSQLQuery(countSb.toString());
		SQLQuery listQuery = session.createSQLQuery(listSb.toString());
	
		if (hospInfo != null) {
			String compCode = hospInfo.getProdCompCode();
			if (StringUtils.isNotBlank(compCode)) {
				countQuery.setString("compCode", compCode);
				listQuery.setString("compCode", compCode);
			}
			
			String prodCompId = hospInfo.getProdCompId();
			if (StringUtils.isNotBlank(prodCompId)) {
				countQuery.setString("prodCompId", prodCompId);
				listQuery.setString("prodCompId", prodCompId);
			}
			
			String hospCode = hospInfo.getHospCode();
			if (StringUtils.isNotBlank(hospCode)) {
				countQuery.setString("hospCode", hospCode);
				listQuery.setString("hospCode", hospCode);
			}
			String hospName = hospInfo.getHospName();
			if (StringUtils.isNotBlank(hospName)) {
				countQuery.setString("hospName", "%" + hospName + "%");
				listQuery.setString("hospName", "%" + hospName + "%");
			}
			String areaId = hospInfo.getAreaId();
			if (StringUtils.isNotBlank(areaId)) {
				countQuery.setString("areaId",areaId+ "%");
				listQuery.setString("areaId",areaId+ "%");
			}
		}

		Long records = ((BigInteger) countQuery.uniqueResult()).longValue();
		page.setRecords(records);

		listQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		listQuery.setFirstResult(page.getFirstResult());
		listQuery.setMaxResults(page.getMaxResults());
		List<Map<String, Object>> list = listQuery.list();

		if (list != null) {
			for (Map<String, Object> map : list) {
				String hospLevelId = (String) map.get("hospLevelId");
				if (StringUtils.isNotBlank(hospLevelId)) {
					String hospLevelName = HospLevel.getName(hospLevelId);
					map.put("hospLevelName", hospLevelName);
				}

				Integer hospClassifyId = new Integer(
						(Byte) map.get("hospClassifyId"));
				if (hospClassifyId != null) {
					String hospClassifyName = HospClassify
							.getName(hospClassifyId);
					map.put("hospClassifyName", hospClassifyName);
				}

			}
		}

		page.setRows(list);

		return page;
	
	}

	@Override
	public Pagination getCatalogByHospSC(SubGoods goods, Pagination page) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sb = new StringBuffer()
		.append(" SELECT distinct b.goods_code goodsCode,a.pro_price proPrice,")
		.append(" 	a.pro_count count,")
		.append(" 	a.bak_pur_count bakPurCount,")
		.append(" 	b.drug_code drugCode,")
		.append(" 	b.qualityspec_name drugName,")
		.append(" 	b.form_name formName,")
		.append(" 	b.spec_name specName,")
		.append(" 	b.factor factor,")
		.append(" 	b.unit_name unit,")
		.append(" 	b.material_name materialName,")
		.append(" 	b.prod_compcode prodCompcode,")
		.append(" 	b.prod_compname prodCompname,")
		.append(" 	b.drug_cat drugCat,")
		.append(" 	b.proj_name projName,")
		.append(" 	b.pro_price_limit proPriceLimit,")
		.append(" 	b.retail_price_limit retailPriceLimit,")
		.append(" 	c.comp_code compCode,")
		.append(" 	c.comp_name psCompName,")
		.append(" 	b.goods_source goodsSource,")
		.append(" 	b.is_longtime_plan isLongTimePlan");
		
		StringBuffer listSb = new StringBuffer()
		.append(" FROM")
		.append(" 	pur_hospcatalog a")
		.append(" JOIN base_goods_item b ON a.goods_code = b.goods_code AND b.drug_status=1 AND ( b.proj_status = 1 OR b.proj_status IS NULL ) AND b. STATUS =1 ");
			if(null!=goods){
				String prodCompcode = goods.getProdCompcode();
				if (StringUtils.isNotBlank(prodCompcode)) {
					listSb.append("and b.prod_compcode=:prodCompcode ");
				}
				String drugCode = goods.getDrugCode();
				if (StringUtils.isNotBlank(drugCode)) {
					listSb.append("and b.drug_code = :drugCode ");
				}
				String drugName = goods.getDrugName();
				if (StringUtils.isNotBlank(drugName)) {
					listSb.append("and b.qualityspec_name like :drugName ");
				}
				Integer isLongTimePlan = goods.getIsLongTimePlan();
				if (isLongTimePlan != null) {
					listSb.append("and b.goods_source =5 and b.is_longtime_plan = :isLongTimePlan ");
				}
			}
		listSb.append(" LEFT JOIN std_compinfo c ON a.del_comp_code = c.comp_code")
		.append(" WHERE 1=1");
		if (goods != null) {
			if(StringUtils.isNotBlank(goods.getHospCode())){
				listSb.append(" and a.hosp_code = :hospCode ");	
			}
			String psCompName = goods.getPsCompName();
			if (StringUtils.isNotBlank(psCompName)) {
				listSb.append("and c.comp_name like :psCompName ");
			}
		}

		StringBuffer countSb = new StringBuffer();
		countSb.append("select count(distinct b.goods_code) ").append(listSb);
		SQLQuery countQuery = session.createSQLQuery(countSb.toString());
		SQLQuery listQuery = session.createSQLQuery(sb.append(listSb).toString());

		
		if (goods != null) {
			if(StringUtils.isNotBlank(goods.getHospCode())){
				countQuery.setString("hospCode", goods.getHospCode());
				listQuery.setString("hospCode", goods.getHospCode());
			}
			
			String prodCompcode = goods.getProdCompcode();
			if (StringUtils.isNotBlank(prodCompcode)) {
				countQuery.setString("prodCompcode", prodCompcode);
				listQuery.setString("prodCompcode", prodCompcode);
			}
			
			String drugCode = goods.getDrugCode();
			if (StringUtils.isNotBlank(drugCode)) {
				countQuery.setString("drugCode", drugCode);
				listQuery.setString("drugCode", drugCode);
			}
			String drugName = goods.getDrugName();
			if (StringUtils.isNotBlank(drugName)) {
				countQuery.setString("drugName", "%" + drugName + "%");
				listQuery.setString("drugName", "%" + drugName + "%");
			}
		
			String psCompName = goods.getPsCompName();
			if (StringUtils.isNotBlank(psCompName)) {
				countQuery.setString("psCompName", "%" + psCompName + "%");
				listQuery.setString("psCompName", "%" + psCompName + "%");
			}
			
			Integer isLongTimePlan = goods.getIsLongTimePlan();
			if (isLongTimePlan != null) {
				countQuery.setInteger("isLongTimePlan", isLongTimePlan);
				listQuery.setInteger("isLongTimePlan", isLongTimePlan);
			}
		}

		Long records = ((BigInteger) countQuery.uniqueResult()).longValue();
		page.setRecords(records);

		listQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		listQuery.setFirstResult(page.getFirstResult());
		listQuery.setMaxResults(page.getMaxResults());
		List<Map<String, Object>> list = listQuery.list();

		if (list != null && !list.isEmpty()) {
			for (Map<String, Object> map : list) {
				String drugCat = map.get("drugCat").toString();
				if (StringUtils.isNotBlank(drugCat)) {
					DicSys dic = DicSysUtil.getDicByClassCodeAndDicCode(
							Constants.DRUG_CAT, drugCat);
					if (dic != null) {
						String drugCatName = dic.getDicName();
						map.put("drugCatName", drugCatName);
					}
				}

				BigDecimal proPrice = (BigDecimal) map.get("proPrice");
				if (proPrice != null) {
					map.put("proPrice",
							ParseNumber.toStrDecimal(proPrice.doubleValue()));
				}

				BigDecimal proPriceLimit = (BigDecimal) map
						.get("proPriceLimit");
				if (proPriceLimit != null) {
					map.put("proPriceLimit", ParseNumber
							.toStrDecimal(proPriceLimit.doubleValue()));
				}

				BigDecimal retailPriceLimit = (BigDecimal) map
						.get("retailPriceLimit");
				if (retailPriceLimit != null) {
					map.put("retailPriceLimit", ParseNumber
							.toStrDecimal(retailPriceLimit.doubleValue()));
				}
				
				String compId = (String) map.get("compId");
				if (StringUtils.isBlank(compId)) {
					map.put("compId", "");
				}
			}
		}
		page.setRows(list);
		return page;
	}
	public void batchDelete(List<String> list,String hospCode){
		String sql = "delete from pur_hospcatalog where goods_code in (:goodsCode) and hosp_code=:hospCode";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("goodsCode", list);
		params.put("hospCode", hospCode);
		dao.executeSql(sql, params);
		String planSql = "delete from pur_purplan where goods_code in (:goodsCode) and hosp_code=:hospCode";
		dao.executeSql(planSql, params);
	}

}
